package com.h819.core.db;

import com.h819.core.commons.Configurations;
import org.apache.commons.configuration.PropertiesConfiguration;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import static java.lang.System.out;

//单例模式和静态变量应用场景
//静态变量，一经调用(在任何一个类中调用)，就会一直驻留在全局中，可以频繁复用，直到程序退出。如果该对象被销毁了，即使再次用代码调用，也不会生成新的实例。
//所以，该变量不应涉及需要被清理的操作
//例如，配置文件可以放在全局中，而数据库连接不应该。
//打开的物理资源，如数据库连接，网络连接，磁盘文件等，要显示的回收。
// java 的垃圾回收机制不会回收任何物理资源，垃圾回收机制只能回收堆内存中对象所占用的资源

//单例模式应用场景
//单例模式和静态方法差不多，区别是单例模式可以继承

/**
 * @Title: DbUtils.java
 * @Description: TODO(数据库工具类，继承了工作常用连接类)
 * @author H819
 * @date 2010-3-18
 * @version V1.0
 */
public class MyDbUtils {

	private static Logger log = Logger.getLogger(MyDbUtils.class);
	private static MyDbUtils instance;

	private static PropertiesConfiguration conf;

	private static String oracleDriver = "oracle.jdbc.driver.OracleDriver";
	private static String mySQLDriver = "com.mysql.jdbc.Driver";

	/**
	 * 静态类，提供静态方法，不允许实例化
	 */
	private MyDbUtils() {
	}

	/**
	 * 应用单例模式，返回类本身。
	 * 
	 * 注意不要把 Connection 设置为静态变量，否则它会一直存在于全局，即使再次调用或者数据库关闭之后的时候，无法再次创建。
	 * 
	 * 属性文件默认为 h819.properties
	 * 
	 * @return
	 */
	public static MyDbUtils getInstanceDefault() {

		return getInstance("h819.properties");
	}

	/**
	 * 构造函数，指定属性文件
	 * 
	 * @param propertiesFile
	 *            在 class path 内的属性文件名称
	 * @return
	 */
	public static MyDbUtils getInstance(String propertiesFile) {
		if (instance == null) {
			conf = Configurations.getInstance(propertiesFile);
			instance = new MyDbUtils();
		}
		return instance;
	}

	/**
	 * 获得数据库连接
	 * 
	 * @param driverName
	 *            驱动名称.(mysql)com.mysql.jdbc.Driver</br>(oracle)oracle.jdbc.driver
	 *            .OracleDriver;
	 * @param connectionURL
	 *            数据库连接 (mysql)jdbc:mysql://localhost:3306/test?useUnicode=true&
	 *            characterEncoding
	 *            =UTF-8</br>(oracle)dbc:oracle:thin:@129.9.200.235:1521:orcl
	 * @param username
	 *            登录名
	 * @param password
	 *            登录密码
	 * @return
	 */
	public Connection getConnection(String driverName, String connectionURL,
			String username, String password) {

		Connection conn = null;
		try {

			// driver 参数
			// (mysql)com.mysql.jdbc.Driver
			// (oracle)oracle.jdbc.driver.OracleDriver
			// (Paradox)com.hxtt.sql.paradox.ParadoxDriver
			// connection 参数
			// (mysql)jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8
			// (oracle)jdbc:oracle:thin:@129.9.200.235:1521:orcl
			// (Paradox)jdbc:paradox:/E:/Titan/Titan/db

			if (conn == null) {
				// 载入驱动程序
				Class.forName(driverName).newInstance();
				// 取得连接
				conn = DriverManager.getConnection(connectionURL, username,
						password);
			}

		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException ex) {
			// handle any errors
			log.info("SQLException: " + ex.getMessage());
			log.info("SQLState: " + ex.getSQLState());
			log.info("VendorError: " + ex.getErrorCode());
		}
		return conn;
	}

	/**
	 * 获得数据库连接
	 * 
	 * @param connectionURL
	 *            数据库连接 (mysql)jdbc:mysql://localhost:3306/test?useUnicode=true&
	 *            characterEncoding
	 *            =UTF-8</br>(oracle)dbc:oracle:thin:@129.9.200.235:1521:orcl
	 * @param username
	 *            登录名
	 * @param password
	 *            登录密码
	 * @return
	 */
	public Connection getConnectionOracle(String connectionURL,
			String username, String password) {

		Connection conn = null;
		try {

			if (conn == null) {
				// 载入驱动程序
				Class.forName(oracleDriver).newInstance();
				// 取得连接
				conn = DriverManager.getConnection(connectionURL, username,
						password);
			}

		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException ex) {
			// handle any errors
			log.info("SQLException: " + ex.getMessage());
			log.info("SQLState: " + ex.getSQLState());
			log.info("VendorError: " + ex.getErrorCode());
		}
		return conn;
	}

	/**
	 * 获得数据库连接
	 * 
	 * @param connectionURL
	 *            数据库连接 (mysql)jdbc:mysql://localhost:3306/test?useUnicode=true&
	 *            characterEncoding
	 *            =UTF-8</br>(oracle)dbc:oracle:thin:@129.9.200.235:1521:orcl
	 * @param username
	 *            登录名
	 * @param password
	 *            登录密码
	 * @return
	 */
	public Connection getConnectionMySQL(String connectionURL, String username,
			String password) {

		Connection conn = null;
		try {

			if (conn == null) {
				// 载入驱动程序
				Class.forName(mySQLDriver).newInstance();
				// 取得连接
				conn = DriverManager.getConnection(connectionURL, username,
						password);
			}

		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException ex) {
			// handle any errors
			log.info("SQLException: " + ex.getMessage());
			log.info("SQLState: " + ex.getSQLState());
			log.info("VendorError: " + ex.getErrorCode());
		}
		return conn;
	}

	/**
	 * 获取 ip=235 oracle 数据库连接。
	 * 
	 * @return
	 */
	public Connection getOracleConnection235() {
		return MyDbUtils.getInstanceDefault().getConnection(
				oracleDriver,
				conf.getString("jdbc.oracle.work.235.url"),
				conf.getString("jdbc.oracle.work.235.user"),
				conf.getString("jdbc.oracle.work.235.password"));
	}

	/**
	 * 获取 ip=235 oracle 数据库连接，只读权限(根据连接用户权限)。
	 * 
	 * @return
	 */
	public Connection getOracleConnectionReadOnly235() {
		return MyDbUtils.getInstanceDefault().getConnection(
				oracleDriver,
				conf.getString("jdbc.oracle.work.235.readonly.url"),
				conf.getString("jdbc.oracle.work.235.readonly.user"),
				conf.getString("jdbc.oracle.work.235.readonly.password"));
	}

	/**
	 * 获取 ip=91 oracle 数据库连接
	 * 
	 * @return
	 */
	public Connection getOracleConnection91() {
		return MyDbUtils.getInstanceDefault().getConnection(
				oracleDriver,
				conf.getString("jdbc.oracle.work.91.url"),
				conf.getString("jdbc.oracle.work.91.user"),
				conf.getString("jdbc.oracle.work.91.password"));
	}

	/**
	 * 获取本机 mysql 数据库连接
	 * 
	 * @return
	 */
	public Connection getConnectionMysqlLocalhost() {
		return MyDbUtils.getInstanceDefault().getConnection(mySQLDriver,
				conf.getString("jdbc.mysql.work.localhost.url"),
				conf.getString("jdbc.mysql.work.localhost.user"),
				conf.getString("jdbc.mysql.work.localhost.password"));
	}

	/**
	 * 获取 ip=136 mysql 数据库连接
	 * 
	 * @return
	 */
	public Connection getConnectionMysql136() {
		return MyDbUtils.getInstanceDefault().getConnection(mySQLDriver,
				conf.getString("jdbc.mysql.work.136.url"),
				conf.getString("jdbc.mysql.work.136.user"),
				conf.getString("jdbc.mysql.work.136.password"));
	}

	public Connection getConnectionParadox() {
		// 连接地址写法
		// String url = "jdbc:paradox:/E:/Titan/Titan/db");
		return MyDbUtils.getInstanceDefault().getConnection(
				"com.hxtt.sql.paradox.ParadoxDriver",
				conf.getString("jdbc.paradox.project.localhost.url"),
				conf.getString("jdbc.paradox.project.localhost.user"),
				conf.getString("jdbc.paradox.project.localhost.password"));
	}

	/**
	 * 检测数据库服务是否可用
	 * 
	 * @param driverName
	 * @param connectionURL
	 * @param username
	 * @param password
	 * @return
	 */
	public boolean isValid(String driverName, String connectionURL,
			String username, String password) {

		// 经过测试，测试 oracle ，只要调用 conn 的方法， conn 就会直接退出，不报任何异常
		boolean inService = false;
		Connection conn = null;

		try {

			if (conn == null) {
				// 载入驱动程序
				Class.forName(driverName).newInstance();
				// 取得连接
				conn = DriverManager.getConnection(connectionURL, username,
						password);

			}

			inService = conn.isValid(20);

			log.info("inService :" + inService);

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();

			inService = false;

		} finally {
			try {
				DbUtils.close(conn);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return inService;
		}
	}

	/**
	 * 探测数据库信息
	 */
	private static void DBVersionDetector() {
		// Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();

		try {
			Connection conn = MyDbUtils.getInstanceDefault().getConnection(
					"com.mysql.jdbc.Driver",
					"jdbc:mysql://localhost:3306/test", "root", "123456");
			DatabaseMetaData meta = conn.getMetaData();
			out.println("URL：\t" + meta.getURL());
			out.println("UserName：\t" + meta.getUserName());
			out.println("DatabaseProductName：\t"
					+ meta.getDatabaseProductName());
			out.println("DatabaseMajorVersion：\t"
					+ meta.getDatabaseMajorVersion());
			out.println("DatabaseMinorVersion：\t"
					+ meta.getDatabaseMinorVersion());
			out.println("DatabaseProductVersion：\t"
					+ meta.getDatabaseProductVersion());
			out.println("DriverName：\t" + meta.getDriverName());
			out.println("DriverMajorVersion：\t" + meta.getDriverMajorVersion());
			out.println("DriverMinorVersion：\t" + meta.getDriverMinorVersion());
			out.println("DriverVersion：\t" + meta.getDriverVersion());
			out.println("JDBCMajorVersion：\t" + meta.getJDBCMajorVersion());
			out.println("JDBCMinorVersion：\t" + meta.getJDBCMinorVersion());
			DbUtils.close(conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		// 运行结果如下（示例）：
		// URL： jdbc:mysql://localhost:3306/test
		// UserName： root@localhost
		// DatabaseProductName： MySQL
		// DatabaseMajorVersion： 5
		// DatabaseMinorVersion： 1
		// DatabaseProductVersion： 5.1.36-community
		// DriverName： MySQL-AB JDBC Driver
		// DriverMajorVersion： 5
		// DriverMinorVersion： 1
		// DriverVersion： mysql-connector-java-5.1.8 ( Revision: ${svn.Revision}
		// )
		// JDBCMajorVersion： 3
		//
		// 从这里能看出
		// 数据版本：5.1.36-community，支持JDBC3.0
		// 驱动是：mysql-connector-java-5.1.8

	}

	/**
	 * 演示工具类用法
	 */
	private void example() {

		Connection conn = MyDbUtils.getInstance("h819.properties")
				.getOracleConnectionReadOnly235();

		QueryRunner runner = new QueryRunner();

		// oracle 中，只读帐号，语句写法
		String sql = "select * from standard.st_standard where standard_code ='GB/T 8625-2005'";

		out.println("sql : " + sql);
		try {
			List<Map<String, Object>> resultList;
			resultList = runner.query(conn, sql, new MapListHandler());

			if (resultList != null) // 如果查不到数据，返回值为 null，故需要判断
				for (Map m : resultList) {
					out.println(StringUtils.center("信息", 80, '*'));

					for (Iterator it = m.entrySet().iterator(); it.hasNext();) { // 每条标准的具体信息
						Map.Entry entry = (Map.Entry) it.next();
						out.println(entry.getKey() + " = " + entry.getValue());
					}
				}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DbUtils.closeQuietly(conn);
		}

	}

	/**
	 * @param args
	 */
	public static void main(String[] args) {

		// 探测数据库信息
		// MyDbUtils.DBVersionDetector();

		// 测试连接类
		MyDbUtils.getInstanceDefault().example();
		// 测试服务是否可用方法
		// mysql
		// out.println(MyDbUtils.getInstance().isValid("com.mysql.jdbc.Driver",
		// "jdbc:mysql://localhost:3306/test", "root", "123456"));

		// oracle
		// out.println(MyDbUtils.getInstance().isValid(
		// "oracle.jdbc.driver.OracleDriver",
		// "jdbc:oracle:thin:@129.9.200.235:1521:orcl", "standard",
		// "standard"));

	}

}
